import sqlite3
from datetime import datetime
import threading
import queue

class ProductDatabase:
    def __init__(self):
        self.db_path = 'products.db'
        self._conn = None
        self.init_db()

    def init_db(self):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            # 创建产品表
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS products (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name TEXT NOT NULL,
                    price REAL NOT NULL,
                    created_date TEXT NOT NULL,
                    modified_date TEXT NOT NULL,
                    created_by TEXT NOT NULL
                )
            ''')
            # 创建设置表
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS settings (
                    key TEXT PRIMARY KEY,
                    value TEXT NOT NULL
                )
            ''')
            conn.commit()

    def get_connection(self):
        return sqlite3.connect(self.db_path)

    def execute_query(self, query, params=()):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, params)
            if query.strip().upper().startswith('SELECT'):
                return cursor.fetchall()
            else:
                conn.commit()
                return None

    def add_product(self, name, price, created_by):
        current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        self.execute_query('''
            INSERT INTO products (name, price, created_date, modified_date, created_by)
            VALUES (?, ?, ?, ?, ?)
        ''', (name, price, current_time, current_time, created_by))

    def update_product(self, id, name, price):
        current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        self.execute_query('''
            UPDATE products
            SET name=?, price=?, modified_date=?
            WHERE id=?
        ''', (name, price, current_time, id))

    def delete_product(self, id):
        self.execute_query('DELETE FROM products WHERE id=?', (id,))

    def search_products(self, name='', sort_by='id', sort_order='ASC', page=1, per_page=10):
        # 在搜索结果中也限制为最新15条
        return self.execute_query(
            f'''
            SELECT * FROM products
            WHERE name LIKE ?
            ORDER BY created_date DESC
            LIMIT 15
            ''',
            (f'%{name}%',)
        )

    def get_all_products(self, sort_by='id', sort_order='ASC', page=1, per_page=10):
        # 默认按创建时间倒序排序
        if sort_by == 'id':
            sort_by = 'created_date'
            sort_order = 'DESC'

        # 限制总数为15条
        return self.execute_query(
            f'SELECT * FROM products ORDER BY created_date DESC LIMIT 15',
            ()
        )

    def save_sort_preference(self, sort_column, sort_order):
        self.execute_query('''
            INSERT OR REPLACE INTO settings (key, value)
            VALUES (?, ?)
        ''', ('sort_column', sort_column))
        self.execute_query('''
            INSERT OR REPLACE INTO settings (key, value)
            VALUES (?, ?)
        ''', ('sort_order', sort_order))

    def get_sort_preference(self):
        try:
            sort_column = self.execute_query(
                'SELECT value FROM settings WHERE key = ?',
                ('sort_column',)
            )
            sort_order = self.execute_query(
                'SELECT value FROM settings WHERE key = ?',
                ('sort_order',)
            )

            # 检查查询结果是否为空
            if not sort_column or not sort_order:
                return {
                    'column': 'id',
                    'order': 'ASC'
                }

            return {
                'column': sort_column[0][0] if sort_column else 'id',
                'order': sort_order[0][0] if sort_order else 'ASC'
            }
        except Exception as e:
            print(f"获取排序偏好时出错: {str(e)}")
            # 返回默认值
            return {
                'column': 'id',
                'order': 'ASC'
            }

    def get_total_count(self):
        result = self.execute_query('SELECT COUNT(*) FROM products')
        return result[0][0] if result else 0

    def get_search_count(self, name=''):
        result = self.execute_query(
            'SELECT COUNT(*) FROM products WHERE name LIKE ?',
            (f'%{name}%',)
        )
        return result[0][0] if result else 0